The DataCombo and DataList Controls

Visual Basic 6 includes two controls that can be used only as bound controls: DataList and DataCombo. These controls are variants of the regular ListBox and ComboBox controls and are special because they can be bound to two different ADO Data controls. The first Data control determines the value to be selected in the control (as is the case for regular ListBox and ComboBox controls); the second Data control fills the list portion.

The DataList and DataCombo controls are often used to provide lookup tables. A lookup table is a secondary table that typically contains the human-friendly description of an entity and is used to transform an encoded value into an intelligible form. For example, the Products table in the NWind.mdb database includes the CategoryID field, which is a number that corresponds to a value in the CategoryID field of the Categories table. Therefore, to display information about a product, you have to perform an INNER JOIN command to retrieve all the requested data:

SELECT ProductName, CategoryName FROM Products INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID

While this approach works when you're processing data via code, it often isn't a viable solution when you're using bound controls. For example, what happens if users are allowed to modify the category of a product? In this case a bullet-proof interface would require that you load all the values in the Categories table into a ListBox or ComboBox control so that users couldn't enter an incorrect category name. This task requires you to open a secondary Recordset, as this code illustrates:

' This code assumes that cn already points to a valid connection. Dim rsCat As New ADODB.Recordset rsCat.Open "SELECT CategoryID, CategoryName FROM Categories", cn lstCategories.Clear Do Until rsCat.EOF lstCategories.AddItem rsCat("CategoryName") lstCategories.ItemData(lstCategories.NewIndex) = rsCat("CategoryID") rsCat.MoveNext Loop rsCat.Close

Of course, you then have to write the code that highlights the correct item in the ListBox when the user navigates through the records in the Products table, as well as the code that modifies the value of the CategoryID field in the Products table when users select a different item in the list. As you see, this deceptively simple job requires more code than you probably thought was necessary. Fortunately, if you set a few design-time properties, the DataCombo and DataList controls can easily accomplish this task.

The DataCombo and DataList controls are included in the MSDATLST.OCX file, which must therefore be distributed with any application that uses these controls.

To implement a lookup table with the DataCombo and DataList controls, you need to place two ADO Data controls on the form, one that points to the main table (Products, in the previous example) and one that points to the lookup table (Categories, in the previous example). Then set, at a minimum, the following properties for the DataCombo and DataList controls:

• DataSource a reference to the main ADO Data control, which in turn points to the main database table.
• DataField the name of the field in the table referred to by the DataSource property and to which this control is bound. This field is updated when a new value is selected in the list.
• RowSource a reference to the secondary ADO Data control, which in turn points to the lookup table. The list portion of the control will be filled with data coming from the lookup table.
• ListField the name of a field in the lookup table. The list portion of the control will be filled with values coming from this field.
• BoundColumn the name of a field in the lookup table. When the user selects a value in the list, the DataField field in the main table receives a value from this column. If you don't assign any value to this property, it will use the same field name as the ListField property.

Let's implement the example described previously. Create an ADO Data control (Adodc1), set it to point to the Products table in NWind.mdb, and add some bound TextBox controls that display fields from that table. Then add another ADO Data control (Adodc2), and set it to retrieve data from the Categories table. Finally, add a DataList control and set its properties as follows: DataSource = Adodc1, DataField = CategoryID, RowSource = Adodc2, ListField = CategoryName, and BoundColumn = CategoryID.

The Products table contains another foreign key, SuppliersID, that points to the Suppliers table. You can implement another lookup mechanism by adding a third ADO Data control (Adodc3), which points to the Suppliers table, and a DataCombo control whose properties should be set as follows: DataSource = Adodc1, DataField = SupplierID, RowSource = Adodc3, ListField = CompanyName, and BoundColumn = SupplierID. You can now run the application, as shown in Figure 15-1.

The DataCombo and DataList controls support other design-time properties, but since in most cases they are the same properties as exposed by the regular ListBox and ComboBox controls, you should already be familiar with them. The only other property you might want to set at design time is MatchEntry, which can take the values 0-dblBasicMatching or 1-dblExtendedMatching. In basic matching mode, when the user presses a key while the focus is on the control, the control highlights the item in the list that begins with the pressed character. In extended matching mode, each character entered is appended to a search string, which is then used to highlight the first matching item, if any. (The search string is automatically reset after a few seconds, or when the BackSpace key is pressed.)

As with all bound controls, DataCombo and DataList expose the DataFormat property, but this property won't deliver the results you probably expect. For example, you can't use DataFormat to change the format of the items in the list. This isn't a bug, however; DataFormat works on the DataField column, whose value is normally hidden from the user when you use these controls. For this reason, the DataFormat property is of limited use with these two controls. The following tip explains how you can format the items in the list.

Figure 15-1. A bound form with two lookup tables.

Working with the DataCombo and DataList controls at run time is similar to working with regular ListBox and ComboBox controls, with a few essential differences. For example, there are no ListIndex and ListCount properties, nor is there an AddItem method to add items to the list at run time. The only way to fill the list portion of these controls is by using an ADO Data control or another data source, such as a Recordset or DataEnvironment instance.

The DataList and DataCombo controls also expose a few peculiar properties. The MatchedWithList read-only property returns True if the value in the edit portion of a DataCombo control matches one of the elements in the list. This property is always True with DataList and DataCombo controls whose Style property is 2dbcDropDownList. The BoundText property returns or sets the value of the field named by the BoundColumn property—that is, the value that will be assigned to the DataField column in the main table.

The SelectedItem property returns a bookmark to the lookup table that corresponds to the highlighted item in the list portion. You usually use this property to display additional information about the selected item. For example, suppose that you want to display the value of the ContactName field from the Suppliers table whenever a new supplier is selected from the list. To do so, create a Label control named lblSupplierData and add the following code to the form module:

Private Sub DataCombo1_Click(Area As Integer) ' Move to the correct record in the lookup table. ' NOTE: The ContactName field must be included in the list ' of fields returned by the Adodc3 data control. If Area = dbcAreaList Then Adodc3.Recordset.Bookmark = DataCombo1.SelectedItem lblSupplierData = Adodc3.Recordset("ContactName") End If End Sub

The DataCombo's Click and DblClick events receive an Area parameter that states which portion of the control has been clicked. Possible values for this parameter are 0-dbcAreaButton, 1-dbcAreaEdit, and 2-dbcAreaList.

The problem with the preceding approach is that the DataList or DataCombo controls' Click event doesn't fire when the user displays a new record in the form. For this reason, you must trap the primary ADO Data control's MoveComplete event:

Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, _ ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) ' You need to manually assign a value to BoundText because the ' SelectedItem property hasn't been updated yet when this event fires. DataCombo1.BoundText = Adodc1.Recordset("SupplierID") ' Simulate a Click to keep the control in sync. DataCombo1_Click dbcAreaList End Sub

The VisibleCount property returns the number of visible items in the list portion. It's intended to be used together with the VisibleItems property, which returns an array of bookmarks to the lookup table that correspond to all the visible items in the list. For example, you might place a lstDescription ListBox control to the right of the DataList1 control and load it with additional information from the lookup table, as you can see in the code below.

Dim i As Long lstDescription.Clear For i = 0 To DataList1.VisibleCount - 1 Adodc2.Recordset.Bookmark = DataList1.VisibleItems(i) lstDescription.AddItem Adodc2.Recordset("Description") Next

The problem here is that you can execute this code whenever a new record becomes current, but it's impossible to keep the lstDescription ListBox in sync with the DataList1 control because the latter lacks a Scroll event. A better use for the VisibleCount and VisibleItems properties is to implement a ToolTip mechanism:

' This code assumes that DataList1.IntegralHeight = True. Private Sub DataList1_MouseMove(Button As Integer, Shift As Integer, _ x As Single, y As Single) ' Determine the item over which the mouse cursor is placed. Dim item As Long item = Int(y / DataList1.Height * DataList1.VisibleCount) ' Retrieve the description for the category under the cursor, and ' prepare a ToolTip in case the user doesn't move the mouse. Adodc2.Recordset.Bookmark = DataList1.VisibleItems(item) DataList1.ToolTipText = Adodc2.Recordset("Description") End Sub

One of the problems of the original Data control, which has been inherited by the newer ADO Data control, is that each instance of the control opens its own connection to the database, with two undesirable consequences. First, if you have multiple Data controls, they can't share the same transaction space. Second, each connection takes resources from the server. If a form uses numerous lookup tables based on DataCombo and DataList controls, your application is going to consume more resources than necessary and might incur problems if there is a shortage of available connections.

When working with ADO data-aware controls, you can often avoid this waste of resources. In fact, your DataCombo and DataList controls don't typically need a visible Data control because the user never actually navigates the lookup table. Therefore, you can obtain the same results using a plain ADO Recordset object. Set the properties of the DataCombo and DataList controls as if they were bound to an ADO Data control for the lookup table, but leave their RowSource property blank. Assign this property at run time, after creating a Recordset object that shares the main ADO Data control's connection:

Dim rsCategories As New ADODB.Recordset Dim rsSuppliers As New ADODB.Recordset Private Sub Form_Load() rsCategories.Open "Categories", Adodc1.Recordset.ActiveConnection Set DataList1.RowSource = rsCategories rsSuppliers.Open "Suppliers", Adodc1.Recordset.ActiveConnection Set DataCombo1.RowSource = rsSuppliers End Sub

So far we've assumed that the lookup table's contents are fixed. In practice, however, the user often needs to add new items to the table, as when he or she inserts a product that comes from a company not yet in the Suppliers table. You can deal with this situation by using DataCombo controls with Style = 0-dbcDropdownCombo. When the main ADO Data control is about to write values to the Products table, your code can check whether the Supplier name is already in the Suppliers table and, if it isn't, ask the user if a new supplier should be created. Here's the minimal code that implements this feature:

Private Sub Adodc1_WillChangeRecord(ByVal adReason As _ ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset) ' Exit if data in DataCombo hasn't been modified ' or if it matches an item in the list. If Not DataCombo1.DataChanged Or DataCombo1.MatchedWithList Then Exit Sub End If ' Ask if the user wants to add a new supplier; cancel operation if not. If MsgBox("Supplier not found." & vbCr & "Do you want to add it?", _ vbYesNo + vbExclamation) = vbNo Then adStatus = adStatusCancel End If ' Add a new record to the Recordset. In a real application, you should ' display a complete data entry form. rsSuppliers.AddNew "CompanyName", DataCombo1.Text rsSuppliers.Update ' Ensure that the new record is visible in the Recordset. rsSuppliers.Requery rsSuppliers.Find "CompanyName = '" & DataCombo1.Text & "'" ' Refill the DataCombo and make the correct item the current one. DataCombo1.ReFill DataCombo1.BoundText = rsSuppliers("SupplierID") End Sub

The preceding code automatically adds a new record to the Suppliers table in a simplified way; a real application should display a complete data entry form in which the user can enter additional data about the new supplier.